﻿
select * from TBL_SALE_PERFORMANCE_KPI

--ALTER TABLE TBL_SALE_PERFORMANCE_KPI ADD BRANCH_ID NVARCHAR(20), REGION NVARCHAR(30)


insert TBL_SALE_PERFORMANCE_KPI
SELECT B.DAO, B.DAO_NAME, A.KPI_ID, A.KPI_GROUP, A.KPI_NAME, A.KPI_UNIT, A.KPI_WEIGHT, A.LEVEL_STAFF, B.GROUP_ID, A.[ORDER], '' AS BRANCH_ID, B.BRANCH_CODE_SME AS REGION FROM TBL_KPI_INFO A, TBL_HR B WHERE A.GROUP_ID = B.GROUP_ID AND B.GROUP_ID = 2 


select * from tbl_hr where group_id = 2

	SELECT (SELECT MAX_MONTH FROM DATE_EM_REPORT) AS YEARMONTH, BRANCH_ID, BRANCH_CODE_SME, BRANCH_NAME_SME, REGION, '' AS TOTAL_SCORE_MONTH, '' as  RANKING_MONTH ,  '' AS TOTAL_SCORE_ACCUMULATED,'' as  RANKING_ACCUMULATED,'' AS  COMPARE_LAST_MONTH
	  FROM TBL_BRANCH


	  select distinct([Lũy kế Xếp loại]) from kpiByBranchSummary201702

	  SELECT ROW_NUMBER() OVER (ORDER BY [LŨY KẾ XẾP LOẠI]) RANK_ID,
       [LŨY KẾ XẾP LOẠI] AS RANK_NAME 
	   --INTO RANKING_KPI
  FROM KPIBYBRANCHSUMMARY201702 GROUP BY  [LŨY KẾ XẾP LOẠI]

  --drop table TBL_KPI_RANK_BRANCH

  SELECT (SELECT MAX_MONTH FROM DATE_EM_REPORT) AS YEARMONTH, B.BRANCH_ID, B.BRANCH_CODE_SME, B.BRANCH_NAME_SME, B.REGION, A.[THỰC HIỆN THÁNG TỔNG ĐIỂM] AS TOTAL_SCORE_MONTH, C.RANK_ID AS  RANKING_MONTH ,  A.[LŨY KẾ TỔNG ĐIỂM] AS TOTAL_SCORE_ACCUMULATED,D.RANK_ID AS  RANKING_ACCUMULATED,A.[SO SÁNH VS THÁNG TRC] AS  COMPARE_LAST_MONTH 
  --INTO TBL_KPI_RANK_BRANCH
  FROM KPIBYBRANCHSUMMARY201702 A, TBL_BRANCH B, RANKING_KPI C, RANKING_KPI D
  WHERE A.MÃ = B.BRANCH_CODE_SME AND A.[THỰC HIỆN THÁNG XẾP LOẠI] = C.RANK_NAME AND A.[LŨY KẾ XẾP LOẠI] = D.RANK_NAME

  select * from TBL_KPI_RANK_BRANCH
  select * from [KPI_Branch_Detail_Thang_201702]

  --alter table [KPI_Branch_Detail_Luyke_201702] drop column F41, F42, F43, F44, F45
  


  SELECT Mã, value_kpi, name_index
FROM 
   (SELECT  * 
   FROM [KPI_Branch_Detail_Thang_201702]) P
UNPIVOT
   (value_kpi FOR name_index IN 
      ([DNBQ TH],[DNBQ KH],[DNBQ TH/KH],[DNBQ Điểm TH],[TCBQ TH],[TCBQ KH],[TCBQ TH/KH],[TCBQ Điểm TH],[DNBQ KH Micro TH],[DNBQ KH Micro KH],[DNBQ KH Micro TH/KH],[DNBQ KH Micro Điểm TH],[HDBQ TH],[HDBQ KH],[HDBQ TH/KH],[HDBQ Điểm TH],[Thu lãi TH],[Thu lãi KH],[Thu lãi TH/KH],[Thu lãi Điểm TH],[Phí + FX TH],[Phí + FX KH],[Phí + FX TH/KH],[Phí + FX Điểm TH],[Tỷ lệ Nợ xấu phát sinh mới TH],[Tỷ lệ Nợ xấu phát sinh mới KH],[Tỷ lệ Nợ xấu phát sinh mới TH/KH],[Tỷ lệ Nợ xấu phát sinh mới Điểm TH],[Thẻ hoạt động mới TH],[Thẻ hoạt động mới KH],[Thẻ hoạt động mới TH/KH],[Thẻ hoạt động mới Điểm TH],[NO Customer TH],[NO Customer KH],[NO Customer TH/KH],[NO Customer Điểm TH] )
)AS UNPVT;


select * from [TRANGDTT18].[SME2017].[dbo].[KPI_MSBO2017_final]




select BRANCH_CODE -- [Mã chi nhánh]
         ,SALE_NAME  --[Tên cán bộ bán hàng]
      ,DAO  --[Mã dao]
      ,POSITION  --[Chức danh]
         ,TIME_WORK  --[Thời gian làm việc (Tháng)]
      ,REGION -- [Vùng]
      ,STAFF_ID  --[Mã nhân viên]
      , DEPT-- [Phòng]
         ,AVGBAL_LOAN_TARGET   --[Kế hoạch dư nợ bình quân(Triệu vnđ)]
         ,AVGBAL_DEPOSIT_TARGET   --[Kế hoạch Huy động bình quân (Triệu vnđ)]
         ,NEWNPL_LOAN_TARGET -- [Kế hoạch nợ xấu phát sinh mới (%)]
      ,AVGBAL_LOAN_UPL_TARGET  -- [Kế hoạch dư nợ tín chấp bình quân(Triệu vnđ)]
         ,CUSTOMER_TARGET  --[Kế hoạch Khách hàng hoạt động tăng thêm]
      ,CREDIT_CARD_TARGET -- [Kế hoạch thẻ phát hành mới]
      ,AVGBAL_LOAN_ACT  -- [Dư nợ bình quân(Triệu vnđ)]
         ,AVGBAL_DEPOSIT_ACT  -- [Huy động bình quân (Triệu vnđ)]
         ,NEWNPL_LOAN_ACT -- [Nợ xấu phát sinh mới (%)]
      ,AVGBAL_LOAN_UPL_ACT  -- [Dư nợ tín chấp bình quân(Triệu vnđ)]
         ,CUSTOMER_ACT  --[Khách hàng hoạt động tăng thêm]
      ,CREDIT_CARD_ACT  --[Thẻ phát hành mới]
      ,AVGBAL_LOAN_REAL   --[Tỉ lệ Hoàn thành Dư nợ bình quân(Triệu vnđ)]
         ,AVGBAL_DEPOSIT_REAL   --[Tỉ lệ Hoàn thành Huy động bình quân (Triệu vnđ)]
         ,NEWNPL_LOAN_REAL  --[Tỉ lệ Hoàn thành Nợ xấu phát sinh mới (%)]
      ,AVGBAL_LOAN_UPL_REAL  -- [Tỉ lệ Hoàn thành Dư nợ tín chấp bình quân(Triệu vnđ)]
         ,CUSTOMER_REAL -- [Tỉ lệ Hoàn thành Khách hàng hoạt động tăng thêm]
      ,CREDIT_CARD_REAL -- [Tỉ lệ Hoàn thành Thẻ phát hành mới]
      ,KPI_FINAL --[% KPIs]
         FROM trangdtt18.SME2017.DBO.FINAL_MSBO_201701
         where AVGBAL_LOAN_UPL_TARGET > 0
         and AVGBAL_LOAN_TARGET > 0
         and AVGBAL_DEPOSIT_TARGET > 0
         and sale_name is not null
         and position is not null
         and dao collate database_default  not in (N'Không có mã DAO',N'Không có mã DAO
')
         and dao collate database_default in ( select dao from trangdtt18.SME2017.DBO.HR_SME_201701 where contract_status =N'Chính thức')
         and dao collate database_default in ( select dao from trangdtt18.SME2017.DBO.KPI_TARGET_201701_CHANGE)
               and BRANCH_CODE not in ('HCM', 'HBH', 'SGN', 'GDH', 'PMH', 'TPU') 
         order by REGION, branch_code, DAO

 select * FROM [TRANGDTT18].[SME2017DAILY].[dbo].[FINAL_MSBO_KQ]
 
SELECT (SELECT DATE_UPDATE FROM DATE_EM_REPORT) AS BUSINESS_DATE,* INTO TRANGDTT18_KPI_RAW FROM [TRANGDTT18].[SME2017DAILY].[DBO].[FINAL_MSBO_20170420_KQ]

select * from TBL_KPI_INFO where GROUP_ID = 4

  SELECT Dao,  value_kpi, name_index
  ,case when name_index like 'AVGBAL_LOAN_%' then 19
		when name_index like 'AVGBAL_DEPOSIT_%' then 20
		when name_index like 'NEWNPL_LOAN_%' then 21
		when name_index like 'AVGBAL_LOAN_UPL_%' then 24
		when name_index like 'CUSTOMER_%' then 25
		when name_index like 'CREDIT_CARD_%' then 26 end kpi_id
	,case when name_index like '%_TARGET' then 'target'
		when name_index like '%_ACT' then 'act'
		when name_index like '%_REAL' then 'real' end classify
		into TRANGDTT18_KPI_RAW_unpivot
  FROM 
   (SELECT  * 
   FROM TRANGDTT18_KPI_RAW) P
UNPIVOT
   (value_kpi FOR name_index IN 
      ([AVGBAL_LOAN_TARGET]      ,[AVGBAL_DEPOSIT_TARGET]      ,[NEWNPL_LOAN_TARGET]      ,[AVGBAL_LOAN_UPL_TARGET]      ,[CUSTOMER_TARGET]      ,[CREDIT_CARD_TARGET]      ,[AVGBAL_LOAN_ACT]      ,[AVGBAL_DEPOSIT_ACT]      ,[NEWNPL_LOAN_ACT]      ,[AVGBAL_LOAN_UPL_ACT]      ,[CUSTOMER_ACT]      ,[CREDIT_CARD_ACT]      ,[AVGBAL_LOAN_REAL]      ,[AVGBAL_DEPOSIT_REAL]      ,[NEWNPL_LOAN_REAL]      ,[AVGBAL_LOAN_UPL_REAL]      ,[CUSTOMER_REAL]      ,[CREDIT_CARD_REAL] )
)AS UNPVT;

select * from TRANGDTT18_KPI_RAW_unpivot


update a
set a.TARGET = b.TARGET, a.real = b.real, a.act = b.act
--select *
from TBL_SALE_PERFORMANCE_KPI a, (select * 
--into TBL_CUSTOMER_CONTACT
 from 
(SELECT dao, kpi_id, classify, value_kpi
FROM TRANGDTT18_KPI_RAW_unpivot ) AS A
PIVOT
(MAX(value_kpi) FOR classify IN ([target],[act],[real])) AS PVT) b
where a.DAO = b.dao collate database_default and a.KPI_ID = b.kpi_id
 
 update a
 set a.BRANCH_ID = c.BRANCH_ID, a.REGION = c.REGION
  from TBL_SALE_PERFORMANCE_KPI a, tbl_hr b, TBL_BRANCH c
 where a.DAO = b.DAO and b.BRANCH_CODE_SME = c.BRANCH_CODE_SME

 
update a
set a.[TARGET_ACCUMULATED] = 0, a.ACT_ACCUMULATED = 0, a.[REAL_ACCUMULATED] = 0
--select *
from TBL_SALE_PERFORMANCE_KPI a
where [REAL_ACCUMULATED] is null


update a
set a.url = c.OBJECT_URL
from TBL_SALE_PERFORMANCE_KPI a, TBL_KPI_INFO b, APPLICATION_OBJECT c
where  a.KPI_ID = b.KPI_ID and a.GROUP_ID = b.GROUP_ID and b.object_id = c.ID

 select * from TBL_SALE_PERFORMANCE_KPI where GROUP_ID = 4 and dao = '14464' and target is null

 select * from TRANGDTT18_KPI_RAW where DAO = '14464'
  --ALTER TABLE TBL_SALE_PERFORMANCE_KPI ADD URL NVARCHAR(250)

  --ALTER TABLE TBL_KPI_INFO ADD OBJECT_ID NVARCHAR(20)

  update TBL_KPI_INFO
  set object_id = '1709'
  from TBL_KPI_INFO where KPI_NAME = N'Dư nợ tín chấp bình quân'

  select * from TBL_KPI_INFO

  select *  from trangdtt18.sme2017daily.[dbo].[RAW_DU_NO_BQ_20170420] 

  drop table TBL_KPI_RAW_DNBQ_DAILY

  SELECT CONVERT(DATE,DATE) AS BUSSINESS_DATE, BRANCH_CODE AS BRANCH_ID, APP, A.DAO, A.DAO_CIF, ACCTNO, A.CIF, AVGBAL_QD, PRODUCT_GROUP, CHUONG_TRINH_SP, ISSUE_DATE, B.CUS_NAME, B.DAO AS DAO_CUST, B.DAO_NAME , D.BRANCH_NAME_SME, D.REGION
  INTO TBL_KPI_RAW_DNBQ_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_DU_NO_BQ_20170420] A, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND A.BRANCH_CODE = D.BRANCH_ID COLLATE DATABASE_DEFAULT

   select * from TBL_KPI_RAW_DNBQ_DAILY 

   	insert TBL_KPI_RAW_DNBQ_DAILY
  SELECT CONVERT(DATE,DATE) AS BUSSINESS_DATE, BRANCH_CODE AS BRANCH_ID, APP, A.DAO, A.DAO_CIF, ACCTNO, A.CIF, AVGBAL_QD, PRODUCT_GROUP,'' as CHUONG_TRINH_SP, ISSUE_DATE, B.CUS_NAME, B.DAO AS DAO_CUST, B.DAO_NAME , D.BRANCH_NAME_SME, D.REGION
  --INTO TBL_KPI_RAW_DNBQ_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_HUY_DONG_BQ_20170420] A, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND A.BRANCH_CODE = D.BRANCH_ID COLLATE DATABASE_DEFAULT


  select * from trangdtt18.sme2017daily.[dbo].[RAW_HUY_DONG_BQ_20170420]
  drop table TBL_KPI_RAW_KHHD_DAILY
  SELECT convert(date, BUSINESS_DATE) as business_date
  ,DAO
  ,ISNULL( SO_LUY_KE,0) as SO_LUY_KE
  ,ISNULL( DEC_2016,0) AS LAST_YEAR
  ,ISNULL( BAN_GIAO,0) as BAN_GIAO
  ,ISNULL( NHAN_BAN_GIAO,0) as NHAN_BAN_GIAO
  ,ISNULL( SO_KHHD_TANG_THEM,0) as SO_KHHD_TANG_THEM
  into TBL_KPI_RAW_KHHD_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_KHHD_TANG_THEM]
   select * from TBL_KPI_RAW_KHHD_DAILY
  select * from trangdtt18.sme2017daily.[dbo].[RAW_KHHD]

  
   	INSERT TBL_KPI_RAW_DNBQ_DAILY
  SELECT CONVERT(DATE,DATE) AS BUSSINESS_DATE, BRANCH_CODE AS BRANCH_ID,'LOANBADBANK' AS APP, A.DAO, A.DAO_CIF, ACCTNO, A.CIF, BAL_QD, PRODUCT_GROUP, CHUONG_TRINH_SP, ISSUE_DATE, B.CUS_NAME, B.DAO AS DAO_CUST, B.DAO_NAME , D.BRANCH_NAME_SME, D.REGION
  --INTO TBL_KPI_RAW_DNBQ_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_NO_XAU_20170420] A, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND A.BRANCH_CODE = D.BRANCH_ID COLLATE DATABASE_DEFAULT

	select * from trangdtt18.sme2017daily.[dbo].[RAW_NO_XAU]
	--drop table TBL_KPI_RAW_CARD_DAILY
	SELECT getdate() as BUSSINESS_DATE, A.CONTRACT_NUMBER, A.CARD_NUMBER, A.CIF, A.CARD_STATUS, A.CARD_DATE_OPEN, A.CARD_TYPE, A.UNLOCK_DATE, A.T24_DAO, A.DAO_CIF, A.CONTRACT_STATUS, A.ACTUAL_DATE_EXPIRE, A.NOTE, B.CUS_NAME, C.BRANCH_NAME_SME, C.REGION
	INTO TBL_KPI_RAW_CARD_DAILY
	FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_THE_20170420] A, TBL_CUSTOMER B, TBL_BRANCH C
	WHERE A.CIF = B.CIF AND B.BRANCH_ID = C.BRANCH_ID

	select * from trangdtt18.sme2017daily.[dbo].[RAW_THE]

	select  CONVERT(DATE,'20170420') as business_date
	,contract_number
	,card_number
	,a.cif
	,card_status
	,card_date_open
	,card_type
	,unlock_date
	,a.dao
	,dao_cif
	,contract_status
	,actual_date_expire
	,note
	,b.CUS_NAME
	,b.BRANCH_ID
	,d.BRANCH_NAME_SME
	,d.REGION
	into TBL_KPI_RAW_CARD_DAILY
	from trangdtt18.sme2017daily.[dbo].[RAW_THE] a, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND b.BRANCH_ID = D.BRANCH_ID COLLATE DATABASE_DEFAULT



	select * from TBL_KPI_RAW_CARD_DAILY where contract_number = '271-C-850567'
	
   	INSERT TBL_KPI_RAW_DNBQ_DAILY
  SELECT CONVERT(DATE,DATE) AS BUSSINESS_DATE, BRANCH_CODE AS BRANCH_ID,'LOANUNSECURED' AS APP, A.DAO, A.DAO_CIF, ACCTNO, A.CIF, AVGBAL_QD, PRODUCT_GROUP, CHUONG_TRINH_SP, ISSUE_DATE, B.CUS_NAME, B.DAO AS DAO_CUST, B.DAO_NAME , D.BRANCH_NAME_SME, D.REGION
  --INTO TBL_KPI_RAW_DNBQ_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_DU_NO_TIN_CHAP_BQ_20170420] A, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND A.BRANCH_CODE = D.BRANCH_ID COLLATE DATABASE_DEFAULT


	select * from trangdtt18.sme2017daily.[dbo].[RAW_DU_NO_TIN_CHAP_BQ_20170420]


	select Dao,  value_kpi, name_index
  ,case when name_index like 'AVGBAL_LOAN_%' then 19
		when name_index like 'AVGBAL_DEPOSIT_%' then 20
		when name_index like 'NEWNPL_LOAN_%' then 21
		when name_index like 'AVGBAL_LOAN_UPL_%' then 24
		when name_index like 'CUSTOMER_%' then 25
		when name_index like 'CREDIT_CARD_%' then 26 end kpi_id
	,case when name_index like '%_TARGET' then 'target'
		when name_index like '%_ACT' then 'act'
		when name_index like '%_REAL' then 'real' end classify
	 FROM 
   (SELECT  * 
   FROM TRANGDTT18_KPI_RAW) P
UNPIVOT
   (value_kpi FOR name_index IN 
      ([AVGBAL_LOAN_TARGET]      ,[AVGBAL_DEPOSIT_TARGET]      ,[NEWNPL_LOAN_TARGET]      ,[AVGBAL_LOAN_UPL_TARGET]      ,[CUSTOMER_TARGET]      ,[CREDIT_CARD_TARGET]      ,[AVGBAL_LOAN_ACT]      ,[AVGBAL_DEPOSIT_ACT]      ,[NEWNPL_LOAN_ACT]      ,[AVGBAL_LOAN_UPL_ACT]      ,[CUSTOMER_ACT]      ,[CREDIT_CARD_ACT]      ,[AVGBAL_LOAN_REAL]      ,[AVGBAL_DEPOSIT_REAL]      ,[NEWNPL_LOAN_REAL]      ,[AVGBAL_LOAN_UPL_REAL]      ,[CUSTOMER_REAL]      ,[CREDIT_CARD_REAL] )
)AS UNPVT
where DAO = '14464'

select * from trangdtt18.sme2017.[dbo].[FINAL_MSBO_201702_LK]


select * from TBL_SALE_PERFORMANCE_KPI where USER_NAME ='trungld1'